Processing queries typically involves at least two phases: a compilation and an execution. During compilation, one or more database server processes perform many functions, such as parsing the query, determining what table(s), column(s), data type(s), etc., are involved, determining whether an index may be used, and generating an execution plan. This process of compilation is typically referred to as a “hard parse.” The execution plan and much information utilized during the compilation stage are saved in a structure referred to as a cursor. During execution, one or more database server processes use the cursor to execute the query.
A query compiler may generate multiple valid execution plans, each of which may be used to generate a valid query result. A query optimizer (which may be the same as or different than the query compiler) selects one of the execution plans for execution. The selection of an execution plan is typically based on an estimated cost of executing the execution plan relative to other candidate execution plans. A query optimizer may take into account several factors to generate an estimated cost, such as the number of rows that may be processed during execution, the number of operations (e.g., joins, table scans) that may be performed, and the number of disk accesses that may be required.
Despite sophisticated attempts at estimating a cost of an execution plan, there may still be circumstances where an execution plan is taking so long that a user (e.g., a database administrator (DBA)) terminates (or “kills”) execution. Such an execution plan is referred to as a “catastrophic plan.” Once a catastrophic plan is terminated, the user must provide input to ensure that that execution plan is not chosen again for the same or similar query. For example, the user may manually alter the contents of the execution plan, such as changing the type of operations and/or the order in which the operations are executed.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.